"""Add analyses table with scoring metrics

Revision ID: 20241002_analysis_scoring
Revises: 20241002_requirement_enhancements
Create Date: 2025-10-02 15:05:00

"""
# pylint: skip-file
# type: ignore
# pyright: reportAttributeAccessIssue=false, reportGeneralTypeIssues=false
from __future__ import annotations

from typing import Sequence, Union

from alembic import op  # type: ignore[import]
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = "20241002_analysis_scoring"
down_revision: Union[str, Sequence[str], None] = "20241002_requirement_enhancements"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


ANALYSIS_COMPLEXITY = sa.Enum(
    "simple",
    "medium",
    "complex",
    name="analysis_complexity",
)

ANALYSIS_RISK = sa.Enum(
    "low",
    "medium",
    "high",
    name="analysis_risk",
)


def upgrade() -> None:
    """Upgrade schema."""
    bind = op.get_bind()
    ANALYSIS_COMPLEXITY.create(bind, checkfirst=True)
    ANALYSIS_RISK.create(bind, checkfirst=True)

    op.create_table(
        "analyses",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "project_id",
            sa.Integer(),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),
        sa.Column("complexity", ANALYSIS_COMPLEXITY, nullable=False),
        sa.Column("estimated_hours", sa.Integer(), nullable=False),
        sa.Column("estimated_cost", sa.Float(), nullable=False),
        sa.Column("risk_level", ANALYSIS_RISK, nullable=False),
        sa.Column("summary", sa.Text(), nullable=False),
        sa.Column("tags", sa.JSON(), nullable=True),
        sa.Column("recommendations", sa.JSON(), nullable=True),
        sa.Column("score", sa.Float(), nullable=False, server_default="0"),
        sa.Column("metrics", sa.JSON(), nullable=False, server_default=sa.text("'{}'")),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
    )
    op.create_index(op.f("ix_analyses_project_id"), "analyses", ["project_id"], unique=True)


def downgrade() -> None:
    """Downgrade schema."""
    op.drop_index(op.f("ix_analyses_project_id"), table_name="analyses")
    op.drop_table("analyses")
    ANALYSIS_RISK.drop(op.get_bind(), checkfirst=True)
    ANALYSIS_COMPLEXITY.drop(op.get_bind(), checkfirst=True)
